package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

import db.DBUtil;
import model.Students;

/**
 * MVC三层模型
 * 模型层
 * @author jinmingwu
 *
 */
public class StudentsDao {
	
	//增加
	public void addStudents(Students stu) throws Exception {
		Connection conn = DBUtil.getConnection();
		String sql = "" + 
				" INSERT INTO information" + 
				" (name,birthday,sex,age,tel)" + 
				" VALUES(" + "?,?,?,?,?)";
		
		//将sql语句加载到驱动执行程序中，但并不直接执行
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		
		ptmt.setString(1, stu.getName());
		ptmt.setString(2, stu.getBirthday());
		ptmt.setString(3, stu.getSex());
		ptmt.setInt(4, stu.getAge());
		ptmt.setString(5, stu.getTel());
		
		//直接sql语句执行
		ptmt.execute();
		
	}
	
	//修改
	public void updateStudents(Students stu) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "" + 
				" UPDATE information" + 
				" SET name=?,birthday=?,sex=?,age=?,tel=? " +
				" WHERE id=? ";
		
		//将sql语句加载到驱动执行程序中，但并不直接执行
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		
		ptmt.setString(1, stu.getName());
		ptmt.setString(2, stu.getBirthday());
		ptmt.setString(3, stu.getSex());
		ptmt.setInt(4, stu.getAge());
		ptmt.setString(5, stu.getTel());
		ptmt.setInt(6, stu.getId());
		
		//直接sql语句执行
		ptmt.execute();
	}
	
	//删除
	public void deleteStudents(Integer p_id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "" + 
				" DELETE FROM information" + 
				" WHERE id=?";
		
		//将sql语句加载到驱动执行程序中，但并不直接执行
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		
		ptmt.setInt(1, p_id);

		//直接sql语句执行
		ptmt.execute();
	}
	
	//查找多个（无查询条件）
	public List<Students> query() throws Exception{
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		ResultSet rs = stmt.executeQuery(" SELECT id,name,age,sex,birthday,tel FROM information");

		List<Students> studentsList = new ArrayList<Students>();
		Students students = null;
		
		//System.out.println("学生姓名\t学号\t年龄");
		while(rs.next()) {
			students = new Students();
			students.setId(rs.getInt(1));
			students.setName(rs.getString(2));
			students.setAge(rs.getInt(3));
			students.setSex(rs.getString(4));
			students.setBirthday(rs.getString(5));
			students.setTel(rs.getString(6));
			
			studentsList.add(students);
			students = null;
			//System.out.println(rs.getString("name") + "\t" + rs.getInt(1) + "\t" + rs.getInt(3));
		}
		
		return studentsList;
	}

	//根据条件查询
	public List<Students> query(List<Map<String, Object>> params) throws Exception{
		Connection conn = DBUtil.getConnection();
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT * FROM information WHERE 1=1 ");
		
		if(params != null && params.size() > 0) {
			for (int i = 0; i < params.size(); i++) {
				Map<String, Object> map = params.get(i);
				sb.append("AND " + map.get("name") + " " + map.get("rela") + " " + map.get("value"));
				
			}
		}
		
		
		//将sql语句加载到驱动执行程序中，但并不直接执行
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sb.toString());
		
		//直接sql语句执行
		//更新操作
		//ptmt.execute();
		
		System.out.println(sb.toString());
		//查询操作
		ResultSet rs = ptmt.executeQuery();

		List<Students> studentsList = new ArrayList<Students>();
		Students students = null;
		
		//System.out.println("学生姓名\t学号\t年龄");
		while(rs.next()) {
			students = new Students();
			students.setId(rs.getInt(1));
			students.setName(rs.getString(2));
			students.setAge(rs.getInt(3));
			students.setSex(rs.getString(4));
			students.setBirthday(rs.getString(5));
			students.setTel(rs.getString(6));
			
			studentsList.add(students);
			students = null;
			//System.out.println(rs.getString("name") + "\t" + rs.getInt(1) + "\t" + rs.getInt(3));
		}
		
		return studentsList;
	}
	
	//查找一个
	public Students get(Integer p_id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "" + 
				" SELECT * FROM information" + 
				" WHERE id=? ";
		
		//将sql语句加载到驱动执行程序中，但并不直接执行
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		
		ptmt.setInt(1, p_id);
		
		//直接sql语句执行
		//更新操作
		//ptmt.execute();
		
		//查询操作
		ResultSet rs = ptmt.executeQuery();
		
		Students student = null;
		while(rs.next()) {
			student = new Students();
			student.setId(rs.getInt("id"));
			student.setName(rs.getString("name"));
			student.setAge(rs.getInt("age"));
			student.setBirthday(rs.getString("birthday"));
			student.setSex(rs.getString("sex"));
			student.setTel(rs.getString("tel"));
		}
		return student;
	}
	
}
